﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

using Lenovo.CFI.Common;

namespace Lenovo.CFI.DAL
{
    public class UserIdeaAppDa
    {
        public static List<UserIdeaApp> GetIdeaApp(string applicant, int? ideaID, string approver, DateTime? timeF, DateTime? timeT, int? status)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetUserIdeaApplication", 
                applicant, ideaID, approver, timeF, timeT, status);

            List<UserIdeaApp> apps = new List<UserIdeaApp>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    apps.Add(PopulateUserIdeaApp(dataReader));
                }
            }

            return apps;
        }

        public static UserIdeaApp GetIdeaAppByID(int id)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetUserIdeaApplicationByID", id);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    return PopulateUserIdeaApp(dataReader);
                }
            }

            return null;
        }

        private static UserIdeaApp PopulateUserIdeaApp(IDataReader dataReader)
        {
            UserIdeaApp app = new UserIdeaApp(dataReader.GetInt32(0));
            app.Applicant = dataReader.GetString(1);
            app.IdeaID = dataReader.GetInt32(2);
            app.AppTime = dataReader.GetDateTime(3);
            app.ApproveReason = dataReader.IsDBNull(4) ? null : dataReader.GetString(4);
            app.Status = dataReader.GetInt32(5);
            app.Approver = dataReader.IsDBNull(6) ? null : dataReader.GetString(6);
            app.ApproveReason = dataReader.IsDBNull(7) ? null : dataReader.GetString(7);

            return app;
        }


        public static void InsertIdeaApp(UserIdeaApp app)
        {
            InsertIdeaApp(app, DBHelper.GetDatabase(), null);
        }
        public static void InsertIdeaApp(UserIdeaApp app, TranscationHelper trans)
        {
            InsertIdeaApp(app, trans.DataBase, trans.Transaction);
        }
        private static void InsertIdeaApp(UserIdeaApp app, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertUserIdeaApplication",
                app.Applicant, app.IdeaID, app.AppTime, app.AppReason, app.Status);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void UpdateIdeaApp(UserIdeaApp app)
        {
            UpdateIdeaApp(app, DBHelper.GetDatabase(), null);
        }
        public static void UpdateIdeaApp(UserIdeaApp app, TranscationHelper trans)
        {
            UpdateIdeaApp(app, trans.DataBase, trans.Transaction);
        }
        private static void UpdateIdeaApp(UserIdeaApp app, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateUserIdeaApplication",
                app.Id, app.Status, app.Approver, app.ApproveReason);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void DeleteIdeaApp(int id)
        {
            DeleteIdeaApp(id, DBHelper.GetDatabase(), null);
        }
        public static void DeleteIdeaApp(int id, TranscationHelper trans)
        {
            DeleteIdeaApp(id, trans.DataBase, trans.Transaction);
        }
        private static void DeleteIdeaApp(int id, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteUserIdeaApplication", id);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }


        public static bool CanAccess(string user, int ideaID)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetUserCanAccessIdea", user, ideaID);

            try
            {
                return Convert.ToInt32(db.ExecuteScalar(dbCommand)) > 0;
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }

            return false;
        }

        public static List<Idea> GetUserIdeaList(string user)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetUserIdeaList", user);

            List<Idea> ideas = new List<Idea>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    ideas.Add(IdeaDa.PopulateIdea(dataReader));
                }
            }

            return ideas;
        }

        public static List<Common.Sys.User> GetIdeaUserList(int ideaID)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetIdeaUserList", ideaID);

            List<Common.Sys.User> users = new List<Common.Sys.User>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    users.Add(DAL.Sys.UserDa.PopulateUser(dataReader));
                }
            }

            return users;
        }


        public static void InsertUserIdeaList(string user, int ideaID)
        {
            InsertUserIdeaList(user, ideaID, DBHelper.GetDatabase(), null);
        }
        public static void InsertUserIdeaList(string user, int ideaID, TranscationHelper trans)
        {
            InsertUserIdeaList(user, ideaID, trans.DataBase, trans.Transaction);
        }
        private static void InsertUserIdeaList(string user, int ideaID, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertUserIdeaList", user, ideaID);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void DeleteUserIdeaList(string user, int ideaID)
        {
            DeleteUserIdeaList(user, ideaID, DBHelper.GetDatabase(), null);
        }
        public static void DeleteUserIdeaList(string user, int ideaID, TranscationHelper trans)
        {
            DeleteUserIdeaList(user, ideaID, trans.DataBase, trans.Transaction);
        }
        private static void DeleteUserIdeaList(string user, int ideaID, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteUserIdeaList", user, ideaID);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }
    }
}
